﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Model;
using System.Data.SqlClient;
using System.Configuration;

namespace DAL
{
    public class AdminDAL
    {

        #region 增删改查
        /// <summary>
        /// 添加信息
        /// </summary>
        /// <returns></returns>
        public int Add<T>(T t)
        {
            return Common.ADD<T>(t);
        }
        /// <summary>
        /// 获取列表
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public List<T> GetList<T>()
        {
            return Common.GetList<T>();
        }
        /// <summary>
        /// 根据编号删除信息
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool DeleteById<T>(int id)
        {
            return Common.DeleteEntity<T>(id);
        }
        /// <summary>
        /// 修改信息
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <returns></returns>
        public bool Update<T>(T t)
        {
            return Common.UpdateEntity<T>(t);
        }

        /// <summary>
        /// 根据编号修改部门的状态
        /// </summary>
        /// <param name="id"></param>
        /// <param name="state"></param>
        /// <returns></returns>
        public int UpdateState(int id, int state)
        {
            string sql = "";
            if (state == 0)
            {
                sql = "update SysDepartment set Department_IsState=1 where ID=" + id;
            }
            else
            {
                sql = "update SysDepartment set Department_IsState=0 where ID=" + id;
            }
            return DBHelper.ExecuteNonQuery(sql); ;
        }

        /// <summary>
        /// 根据编号角色的状态
        /// </summary>
        /// <param name="id"></param>
        /// <param name="state"></param>
        /// <returns></returns>
        public int UpdateRoleState(int id, int state)
        {
            string sql = "";
            if (state == 0)
            {
                sql = "update [SysRoles] set Roles_IsState=1 where ID=" + id;
            }
            else
            {
                sql = "update [SysRoles] set Roles_IsState=0 where ID=" + id;
            }
            return DBHelper.ExecuteNonQuery(sql); ;
        }

        /// <summary>
        /// 根据编号用户的状态
        /// </summary>
        /// <param name="id"></param>
        /// <param name="state"></param>
        /// <returns></returns>
        public int UpdateUsersState(int id, int state)
        {
            string sql = "";
            if (state == 0)
            {
                sql = "update SysUser set Users_IsState=1 where ID=" + id;
            }
            else
            {
                sql = "update SysUser set Users_IsState=0 where ID=" + id;
            }
            return DBHelper.ExecuteNonQuery(sql); ;
        }
        #endregion

        public static string connString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString();

        #region 根据角色编号获取对应的权限
        /// <summary>
        /// 根据角色编号获取对应的权限
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public List<SysAuthorityAndRoles> GetListByRoleId(int id)
        {
            string sql = "select {0} from [{1}] where Roles_id=" + id;
            Type type = typeof(SysAuthorityAndRoles);
            var SelectField = string.Join(",", type.GetProperties().Select(p => string.Format("{0}", p.Name)));
            sql = string.Format(sql, SelectField, type.Name);
            List<SysAuthorityAndRoles> list = new List<SysAuthorityAndRoles>();
            using (SqlConnection con = new SqlConnection(connString))
            {
                con.Open();
                SqlCommand com = new SqlCommand(sql, con);
                SqlDataReader reader = com.ExecuteReader();
                while (reader.Read())
                {
                    //	var oObject = (T)Activator.CreateInstance(type);
                    SysAuthorityAndRoles saar = new SysAuthorityAndRoles();
                    foreach (var item in type.GetProperties())
                    {
                        object oValue = reader[item.Name];
                        if (oValue is DBNull)
                        {
                            item.SetValue(saar, null);
                        }
                        else
                        {
                            item.SetValue(saar, oValue);
                        }
                    }
                    list.Add(saar);
                }
            }
            return list;
        }
        #endregion

        #region 显示登记表
        /// <summary>
        /// 显示登记表
        /// </summary>
        /// <returns></returns>
        public List<_Register> RegisterList()
        {
            string sql = "select * from EABasic_Info a join dbo.EALoan_Info b on a.ProjectNumber=b.ProjectNumber";
            Type type = typeof(_Register);
            var SelectField = string.Join(",", type.GetProperties().Select(p => string.Format("{0}", p.Name)));
            sql = string.Format(sql, SelectField, type.Name);
            List<_Register> list = new List<_Register>();
            using (SqlConnection con = new SqlConnection(connString))
            {
                con.Open();
                SqlCommand com = new SqlCommand(sql, con);
                SqlDataReader reader = com.ExecuteReader();
                while (reader.Read())
                {
                    _Register _r = new _Register();
                    foreach (var item in type.GetProperties())
                    {
                        object oValue = reader[item.Name];
                        if (oValue is DBNull)
                        {
                            item.SetValue(_r, null);
                        }
                        else
                        {
                            item.SetValue(_r, oValue);
                        }
                    }
                    list.Add(_r);
                }
            }
            return list;
        }
        #endregion

        #region 根据部门编号获取勘查员信息
        public List<SysUser> GetListByDeptId_3(int id)
        {
            string sql = "select {0} from [{1}] where Department_Id = @id and Roles_id=3";
            Type type = typeof(SysUser);
            var SelectField = string.Join(",", type.GetProperties().Select(p => string.Format("{0}", p.Name)));
            sql = string.Format(sql, SelectField, type.Name);
            List<SysUser> list = new List<SysUser>();
            using (SqlConnection con = new SqlConnection(connString))
            {
                con.Open();
                SqlCommand com = new SqlCommand(sql, con);
                com.Parameters.Add(new SqlParameter("@id", id));
                SqlDataReader reader = com.ExecuteReader();
                while (reader.Read())
                {
                    SysUser user = new SysUser();
                    foreach (var item in type.GetProperties())
                    {
                        object oValue = reader[item.Name];
                        if (oValue is DBNull)
                        {
                            item.SetValue(user, null);
                        }
                        else
                        {
                            item.SetValue(user, oValue);
                        }
                    }
                    list.Add(user);
                }
            }
            return list;
        }
        #endregion

        #region 根据部门编号获取估价员员信息
        public List<SysUser> GetListByDeptId_4(int id)
        {
            string sql = "select {0} from [{1}] where Department_Id = @id and Roles_id=4";
            Type type = typeof(SysUser);
            var SelectField = string.Join(",", type.GetProperties().Select(p => string.Format("{0}", p.Name)));
            sql = string.Format(sql, SelectField, type.Name);
            List<SysUser> list = new List<SysUser>();
            using (SqlConnection con = new SqlConnection(connString))
            {
                con.Open();
                SqlCommand com = new SqlCommand(sql, con);
                com.Parameters.Add(new SqlParameter("@id", id));
                SqlDataReader reader = com.ExecuteReader();
                while (reader.Read())
                {
                    SysUser user = new SysUser();
                    foreach (var item in type.GetProperties())
                    {
                        object oValue = reader[item.Name];
                        if (oValue is DBNull)
                        {
                            item.SetValue(user, null);
                        }
                        else
                        {
                            item.SetValue(user, oValue);
                        }
                    }
                    list.Add(user);
                }
            }
            return list;
        }
        #endregion

        #region 获取数据库中的最后一个项目号
        /// <summary>
        /// 获取数据库中的最后一个项目号
        /// </summary>
        /// <returns></returns>
        public string SelectProjectNumber()
        {
            using (SqlConnection con = new SqlConnection(connString))
            {
                con.Open();
                string sql = "select top 1 ProjectNumber from EABasic_Info order by ProjectNumber desc";
                SqlCommand com = new SqlCommand(sql, con);
                if (com.ExecuteScalar() == null)
                {
                    return "";
                }
                return com.ExecuteScalar().ToString();
            }
        }
        #endregion

        #region 修改项目状态为已勘察
        /// <summary>
        /// 修改项目状态为已勘察
        /// </summary>
        /// <param name="number"></param>
        /// <returns></returns>
        public int UpdateState(string number)
        {
            using (SqlConnection con = new SqlConnection(connString))
            {
                con.Open();
                string sql = "update EABasic_Info set Basic_State=2 ,Survey_EndTime=getdate() where ProjectNumber='" + number + "' ";
                SqlCommand com = new SqlCommand(sql, con);
                return com.ExecuteNonQuery();
            }
        }
        #endregion

        #region  根据ProjectNumber 进行查询
        public T GetAssessmentProjectNumber<T>(string ProjectNumber)
        {
            string SqlText = "select {0} from [{1}] where ProjectNumber = @ProjectNumber";
            Type type = typeof(T);
            var SelectField = string.Join(",", type.GetProperties().Select(p => string.Format("[{0}]", p.Name)));
            SqlText = string.Format(SqlText, SelectField, type.Name);
            var oObject = (T)Activator.CreateInstance(type);
            using (SqlConnection con = new SqlConnection(connString))
            {
                con.Open();
                SqlCommand com = new SqlCommand(SqlText, con);
                com.Parameters.Add(new SqlParameter("@ProjectNumber", ProjectNumber));
                SqlDataReader reader = com.ExecuteReader();
                while (reader.Read())
                {
                    foreach (var pop in type.GetProperties())
                    {
                        object oValue = reader[pop.Name];
                        if (oValue is DBNull)
                        {
                            pop.SetValue(oObject, null);
                        }
                        else
                        {
                            pop.SetValue(oObject, oValue);
                        }
                    }
                }
            }
            return oObject;
        }
        #endregion

        #region 根据项目编号 修改为已评估状态
        /// <summary>
        /// 修改为已评估状态
        /// </summary>
        /// <param name="number"></param>
        /// <returns></returns>
        public int UpdateState(EABasic_Info eabasic)
        {
            using (SqlConnection con = new SqlConnection(connString))
            {
                con.Open();
                string sql = @"update EABasic_Info set Basic_State=3, Basic_Evaluation='" + eabasic.Basic_Evaluation
                    + "',Basic_ValueTime='" + eabasic.Basic_ValueTime + "',Basic_UnitPrice='" + eabasic.Basic_UnitPrice
                    + "',Basic_Acreage='" + eabasic.Basic_Acreage
                    + "',Basic_Total='" + eabasic.Basic_Total + "',Basic_Enclosure='" + eabasic.Basic_Enclosure
                    + "',Basic_Excl='" + eabasic.Basic_Excl + "' where ProjectNumber='" + eabasic.ProjectNumber + "'";
                SqlCommand com = new SqlCommand(sql, con);
                return com.ExecuteNonQuery();
            }
        }
        #endregion

        #region 根据项目编号查询审核详情
        /// <summary>
        /// 根据项目编号查询审核详情
        /// </summary>
        /// <param name="ProjectNumber"></param>
        /// <returns></returns>
        public List<EAAuditing> GetAuditDetails(string ProjectNumber)
        {
            string sql = "select {0} from [{1}] where ProjectNumber= @id";
            Type type = typeof(EAAuditing);
            var SelectField = string.Join(",", type.GetProperties().Select(p => string.Format("{0}", p.Name)));
            sql = string.Format(sql, SelectField, type.Name);
            List<EAAuditing> list = new List<EAAuditing>();
            using (SqlConnection con = new SqlConnection(connString))
            {
                con.Open();
                SqlCommand com = new SqlCommand(sql, con);
                com.Parameters.Add(new SqlParameter("@id", ProjectNumber));
                SqlDataReader reader = com.ExecuteReader();
                while (reader.Read())
                {
                    EAAuditing eaauditing = new EAAuditing();
                    foreach (var item in type.GetProperties())
                    {
                        object oValue = reader[item.Name];
                        if (oValue is DBNull)
                        {
                            item.SetValue(eaauditing, null);
                        }
                        else
                        {
                            item.SetValue(eaauditing, oValue);
                        }
                    }
                    list.Add(eaauditing);
                }
            }
            return list;
        }
        #endregion
    }
}
